﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using OneSoft.EntityCore;
using OneSoft.DataAccess;
using BySoft.Server.Common.Entity;
using BySoft.Server.Common.Schema;
using BySoft.Server.Consts;

namespace BySoft.Server.DataAccess
{
    //modify by Pater at 2009-09-10
    public partial class SysUserroleDA
    {
        public static DataTable GetRoleByUser(string userId)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = " SELECT A." + SysRoleTable.C_ROLENAME + ",A." + SysRoleTable.C_ROLEID;
            sSql += " FROM " + SysRoleTable.C_TABLENAME + " A," + SysUserroleTable.C_TABLENAME + " B";
            sSql += " WHERE A." + SysRoleTable.C_ROLEID + " = B." + SysUserroleTable.C_ROLEID;
            if (!String.IsNullOrEmpty(userId))
            {
                sSql += " AND B." + SysUserroleTable.C_USERID + " =@userId";
                parameters.AddWithValue("@userId", userId);
            }

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }

        public static DataTable GetRoleByUserAndUserType(string userId)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();

            string sSql = " SELECT A." + SysRoleTable.C_ROLENAME + ",A." + SysRoleTable.C_ROLEID;
            sSql += " FROM " + SysRoleTable.C_TABLENAME + " A "; 
            sSql += " inner join "+ SysUserroleTable.C_TABLENAME + " B ";
            sSql += " on A."+ SysRoleTable.C_ROLEID + " = B." + SysUserroleTable.C_ROLEID;
            sSql += " WHERE 1=1 ";
            if (!String.IsNullOrEmpty(userId))
            {
                sSql += " AND B." + SysUserroleTable.C_USERID + " =@userId";
            }
            sSql += " union all ";
            sSql += " SELECT C.RoleName,C.RoleID ";
            sSql += " FROM SYS_User A ";
            sSql += " INNER JOIN SYS_UserTypeRole B ON A.EmployeeType=B.UserType ";
            sSql += " INNER JOIN SYS_Role C ON B.RoleID=C.RoleID ";
            sSql += " WHERE 1=1 ";
            if (!String.IsNullOrEmpty(userId))
            {
                sSql += " AND A.UserID=@userId";
                parameters.AddWithValue("@userId", userId);
            }


            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }

        public static DataTable GetRoleRightsByUser(string userId)
        {
            #region SQL Server case  IsNull(t.LASTESTLOGINDATE,1) when   1 then GETDATE() else t.LASTESTLOGINDATE end
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string sSql = " SELECT distinct c.ModuleName,c.OrderNo,a.DataRights as datars,";
            sSql += " case  IsNull((select b.RoleRightsID from SYS_RoleRights b where a.RoleRightsID = b.RoleRightsID and b.rightscode like '%R%'),0) when   0 then '无' else '有' end as viewright,";
            sSql += " case  IsNull((select b.RoleRightsID from SYS_RoleRights b where a.RoleRightsID = b.RoleRightsID and b.rightscode like '%C%'),0) when   0 then '无' else '有' end as addright,";
            sSql += " case  IsNull((select b.RoleRightsID from SYS_RoleRights b where a.RoleRightsID = b.RoleRightsID and b.rightscode like '%U%'),0) when   0 then '无' else '有' end as updateright,";
            sSql += " case  IsNull((select b.RoleRightsID from SYS_RoleRights b where a.RoleRightsID = b.RoleRightsID and b.rightscode like '%D%'),0) when   0 then '无' else '有' end as deleteright,";
            sSql += " case  IsNull((select b.RoleRightsID from SYS_RoleRights b where a.RoleRightsID = b.RoleRightsID and b.rightscode like '%E%'),0) when   0 then '无' else '有' end as exportright,";
            sSql += " (case a.DataRights when '1' then '全部' when '2' then '本部门' when '3' then '本人管辖' end) as DataRights";
            sSql += " FROM SYS_RoleRights a,SYS_Module c,SYS_UserRole d";
            sSql += " WHERE a.ModulePrimaryID = c.ModulePrimaryID";
            sSql += " AND a.RoleID = d.RoleID";
            if (!String.IsNullOrEmpty(userId))
            {
                sSql += " AND d.UserID =@userId";
                parameters.AddWithValue("@userId", userId);
            }
            sSql += " order by c.OrderNo ";
            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }

        public static DataTable GetRoleRightByUserAndUrl(string userId, string url)
        {
            #region SQL Server
            DataAccessParameterCollection parameters = new DataAccessParameterCollection();
            string sSql = @"select a.* ,sm.MODULERIGHTS from userrights_view a
            LEFT JOIN SYS_Module sm ON sm.ModulePrimaryID=a.ModulePrimaryID

            ";
            sSql += " where lower(a." + SysModuleTable.C_FUNCTIONURL + ")=@url";
            sSql += " and a." + SysUserTable.C_USERID + "=@userId";

            parameters.AddWithValue("@userId", userId);
            parameters.AddWithValue("@url", url.ToLower());

            return DataAccessCommon.Select(sSql, parameters).Tables[0];
            #endregion
        }
    }
}
